import os
import shutil
import sys
import time
import traceback

import pypyodbc
from tqdm import tqdm
from xjlibrary.our_file_dir import BaseDir

current_path = os.path.dirname(os.path.abspath(__file__))

sql_path = os.path.join(current_path, "kmxxk.sql")


def get_db_conn(db_full_path):
    """
    数据库连接
    :param db_full_path: 数据库的全路径
    :return: 返回数据库连接对象
    """
    db_extr = os.path.splitext(db_full_path)[1].lower()
    # if db_extr == ".db3":
    #     try:
    #         db3_conn = sqlite3.connect(db_full_path)
    #         print("DB3数据库 %s 连接成功" % db_full_path)
    #         return db3_conn
    #     except Exception as e:
    #         print("数据库连接失败: %s" % e)

    if db_extr == ".mdb" or db_extr == ".accdb":
        try:
            print(db_full_path)
            access_conn = pypyodbc.win_connect_mdb(db_full_path)
            print("ACCESS 数据库 %s 连接成功" % db_full_path)
            return access_conn
        except Exception as e:
            print("数据库连接失败: %s" % e)
            sys.exit(-1)


def deal_sql_set(sql_set, db_conn):
    if len(sql_set) < 1:
        return

    try:
        cur = db_conn.cursor()
        for sql in sql_set:
            # print('input sql: %s' % sql)
            cur.execute(sql)
        db_conn.commit()
    except Exception as e:
        db_conn.rollback()
        print(e)

    print("数据库更新完毕,本次共 %s 条数据更新" % len(sql_set))
    print("*" * 20)


def temp_coy_to_db(temp_path, out_path, name):
    create_time = time.strftime("%Y%m%d", time.localtime(time.time()))
    if not os.path.exists(temp_path):
        print('no template access db plz create one and try again...')
        sys.exit(-1)
    file_name = name + "_" + create_time + os.path.splitext(temp_path)[-1]
    out_put_file = os.path.join(out_path, file_name)
    out_put_file_dir = os.path.dirname(out_put_file)
    if not os.path.exists(out_put_file_dir):
        os.makedirs(out_put_file_dir)
    shutil.copy(temp_path, out_put_file)
    return out_put_file


def to_mdb():
    temp_db = "期刊信息解析模版.mdb"
    temp_db_path = current_path + "\\" + temp_db
    out_put_file = temp_coy_to_db(temp_db_path, current_path, "期刊信息")
    db_conn = get_db_conn(out_put_file)
    print('解析列表页开始...')
    bath_up_list = []
    up_cnt = 0
    for sql in tqdm(BaseDir.read_line(sql_path, "utf8")):
        try:
            bath_up_list.append(sql)
            if len(bath_up_list) >= 100:
                up_cnt += len(bath_up_list)
                deal_sql_set(bath_up_list, db_conn)
                bath_up_list.clear()
                print("Done update rows:" + str(up_cnt))
        except Exception as e:
            print(traceback.format_exc())
    if len(bath_up_list) >= 0:
        up_cnt += len(bath_up_list)
        deal_sql_set(bath_up_list, db_conn)
        bath_up_list.clear()
        print("Done update rows:" + str(up_cnt))


if __name__ == '__main__':
    to_mdb()
